Defining sort order using parameter fields

To set the sort order using parameter fields, you need to first create a formula that includes a parameter field and then sort based on that formula. For example, assume that you have a customer list report based on the Customer table. For each customer, you show the Customer Name, City, Region, Country, and Phone Number. You want to be able to sort the report by Country, by Region, or by City, depending on your needs at the time.

  1. Create a parameter field and call it SortField.
  2. In the Prompting text edit box, enter a prompt similar to this:

    Type R to sort by Region or C to sort by City; otherwise, data will be sorted by Country.

  3. Select String from the Value type drop-down list.
  4. You may want to limit the number of characters the user can type to one. To do this, click Set default values.

    The Set Default Values dialog box appears.

  5. Select the Length limit check box and type the numeral 1 into the Min Length and Max Length text boxes and click OK.

    Now the parameter field will only accept single-character values. The field will accept "C" as a value, but not "City."

  6. Create a formula similar to this and call it Sort:

    If {?SortField} = "C" Then {customer.CITY}

    Else

        If {?SortField} = "R" Then {customer.REGION}

        Else

            {customer.COUNTRY}

This formula prompts for a value for the parameter field {?SortField}. If you enter "C", the formula will sort by the City field. If you enter "R" it will sort by the Region field. If you enter anything else, or do not enter anything at all, the formula will sort by the Country field.

For more information see If statements (Basic syntax).

  1. Place the formula in the Report Header section of the report and select Suppress (No Drill-Down) in the Section Expert so that it does not print.
  2. Click Sort Records.
  3. Choose your formula and click Add.
  4. Click OK.

Now when you run the report, the program will prompt you for a sort field, the formula will return a value based on your selection, and the sort facility will use that value as your sort field.



Seagate Software IMG Holdings, Inc.
http://www.seagatesoftware.com
Support services:
http://support.seagatesoftware.com